package com.iplant.basic.utils;

import com.iplant.base.po.excel.ExcelCellData;
import com.iplant.base.po.excel.MyExcelSheet;
import com.iplant.base.utils.excel.MESFileUtils;
import com.iplant.base.utils.general.Configuration;
import com.iplant.base.utils.general.StringUtils;
import org.apache.poi.common.usermodel.HyperlinkType;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.usermodel.ClientAnchor.AnchorType;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import javax.imageio.ImageIO;
import java.awt.image.BufferedImage;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

public class ExcelUtil {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);

    static HSSFWorkbook mWorkbook;

    static Sheet mSheet;

    static String[] mHeads;

    public static void CreateFirst(String[] wHeaders, String wSheetName) {
        try {
            mWorkbook = new HSSFWorkbook();

            mSheet = mWorkbook.createSheet(wSheetName);

            CellStyle wStyle1 = Style2();

            Row wRow = mSheet.createRow(0);
            wRow.setHeight((short) 500);

            mHeads = wHeaders;

            Cell wCell = null;

            for (int i = 0; i < mHeads.length; i++) {
                wCell = wRow.createCell(i);
                wCell.setCellValue(mHeads[i]);
                wCell.setCellStyle(wStyle1);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    private static CellStyle TitleStyle() {
        HSSFCellStyle wCellStyle = mWorkbook.createCellStyle();
        try {
            wCellStyle.setAlignment(HorizontalAlignment.CENTER);
            wCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            wCellStyle.setWrapText(true);

            HSSFFont wFont = mWorkbook.createFont();
            wFont.setBold(true);
            wFont.setFontName("微软雅黑");
            wFont.setFontHeightInPoints((short) 15);
            wCellStyle.setFont((Font) wFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wCellStyle;
    }

    private static CellStyle Style2() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
            hSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle HeadStyle() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
            hSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();

            hSSFFont.setColor(IndexedColors.WHITE.getIndex());
            hSSFFont.setBold(true);
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle LinkStyle() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();
            hSSFFont.setColor(IndexedColors.BLUE_GREY.getIndex());
            hSSFFont.setUnderline(Font.U_SINGLE);
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle StepStyle() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.LEFT);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
            hSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();
            hSSFFont.setBold(true);
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle ItemStyle() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle NoneStyle() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setBorderTop(BorderStyle.NONE);
            hSSFCellStyle.setBorderBottom(BorderStyle.NONE);
            hSSFCellStyle.setBorderRight(BorderStyle.NONE);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle LeftStyle() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.LEFT);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle ItemStyle_NoBorder() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle ItemStyle_Purple() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();
            hSSFFont.setColor(IndexedColors.VIOLET.getIndex());
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle ItemStyle_Red() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();
            hSSFFont.setColor(IndexedColors.RED.getIndex());
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle ItemStyle_Blue() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();
            hSSFFont.setColor(IndexedColors.BLUE.getIndex());
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle GroupStyle() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.LEFT);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle Style6() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setFillForegroundColor(IndexedColors.LIGHT_CORNFLOWER_BLUE.getIndex());
            hSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();
            hSSFFont.setBold(true);
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle Style7() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setFillForegroundColor(IndexedColors.GREEN.getIndex());
            hSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();

            hSSFFont.setColor(IndexedColors.BLACK.getIndex());
            hSSFFont.setBold(true);
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle Style8() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setFillForegroundColor(IndexedColors.GREY_40_PERCENT.getIndex());
            hSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            HSSFFont hSSFFont = mWorkbook.createFont();

            hSSFFont.setColor(IndexedColors.BLACK.getIndex());
            hSSFFont.setBold(true);
            hSSFCellStyle.setFont((Font) hSSFFont);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle Style9() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
            hSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    private static CellStyle Style10() {
        HSSFCellStyle hSSFCellStyle = mWorkbook.createCellStyle();
        try {
            hSSFCellStyle.setAlignment(HorizontalAlignment.CENTER);
            hSSFCellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            hSSFCellStyle.setFillForegroundColor(IndexedColors.LIME.getIndex());
            hSSFCellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            hSSFCellStyle.setWrapText(true);
            hSSFCellStyle.setBorderLeft(BorderStyle.THIN);
            hSSFCellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderTop(BorderStyle.THIN);
            hSSFCellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderBottom(BorderStyle.THIN);
            hSSFCellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            hSSFCellStyle.setBorderRight(BorderStyle.THIN);
            hSSFCellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return hSSFCellStyle;
    }

    public static void CreateOtherRow_Tip() {
        try {
            HSSFCellStyle wStyle = mWorkbook.createCellStyle();
            wStyle.setAlignment(HorizontalAlignment.CENTER);
            wStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            wStyle.setWrapText(true);

            wStyle.setBorderLeft(BorderStyle.THIN);
            wStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderTop(BorderStyle.THIN);
            wStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderRight(BorderStyle.THIN);
            wStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderBottom(BorderStyle.THIN);
            wStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

            List<String> wRow1 = new ArrayList<>(Arrays.asList("1新造\n2检修", "车型编号", "目前只允许5、6", "2位的局段编号",
                    "固定值1900", "工位编号", "工序描述", "零部件编号", "物料描述，仅在EXCEL显示，系统不检查", "用量", "用量单位", "必换填1，偶换填2，其他不填",
                    "数值≥0并且<100", "委外必修1、委外偶修2、其他不填", "X或空", "X或空", ""));

            Row wHSSFRow = mSheet.createRow(2);

            for (int i = 0; i < wRow1.size(); i++) {
                Cell wHSSFCell = wHSSFRow.createCell(i);
                wHSSFCell.setCellValue(wRow1.get(i));
                wHSSFCell.setCellStyle(wStyle);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void CreateOtherRow_Orange() {
        try {
            HSSFCellStyle wStyle = mWorkbook.createCellStyle();
            wStyle.setAlignment(HorizontalAlignment.CENTER);
            wStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            wStyle.setWrapText(true);

            wStyle.setBorderLeft(BorderStyle.THIN);
            wStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderTop(BorderStyle.THIN);
            wStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderRight(BorderStyle.THIN);
            wStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderBottom(BorderStyle.THIN);
            wStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setFillForegroundColor(IndexedColors.ORANGE.getIndex());
            wStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            HSSFCellStyle wStyle1 = mWorkbook.createCellStyle();
            wStyle1.setAlignment(HorizontalAlignment.CENTER);
            wStyle1.setVerticalAlignment(VerticalAlignment.CENTER);
            wStyle1.setWrapText(true);

            wStyle1.setBorderLeft(BorderStyle.THIN);
            wStyle1.setLeftBorderColor(IndexedColors.BLACK.getIndex());

            wStyle1.setBorderTop(BorderStyle.THIN);
            wStyle1.setTopBorderColor(IndexedColors.BLACK.getIndex());

            wStyle1.setBorderRight(BorderStyle.THIN);
            wStyle1.setRightBorderColor(IndexedColors.BLACK.getIndex());

            wStyle1.setBorderBottom(BorderStyle.THIN);
            wStyle1.setBottomBorderColor(IndexedColors.BLACK.getIndex());

            wStyle1.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.getIndex());
            wStyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            Row wHSSFRow = mSheet.createRow(3);

            Cell wHSSFCell = null;
            for (int i = 0; i < 8; i++) {
                wHSSFCell = wHSSFRow.createCell(i);
                wHSSFCell.setCellValue("必输");
                wHSSFCell.setCellStyle(wStyle);
            }

            wHSSFCell = wHSSFRow.createCell(8);
            wHSSFCell.setCellValue("可选");
            wHSSFCell.setCellStyle(wStyle1);

            wHSSFCell = wHSSFRow.createCell(9);
            wHSSFCell.setCellValue("必输");
            wHSSFCell.setCellStyle(wStyle);

            wHSSFCell = wHSSFRow.createCell(10);
            wHSSFCell.setCellValue("必输");
            wHSSFCell.setCellStyle(wStyle);

            for (int i = 11; i < 17; i++) {
                wHSSFCell = wHSSFRow.createCell(i);
                wHSSFCell.setCellValue("可选");
                wHSSFCell.setCellStyle(wStyle1);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void CreateOtherRow(List<List<String>> wRowList) {
        try {
            if (wRowList == null || wRowList.size() <= 0) {
                return;
            }

            HSSFCellStyle wStyle = mWorkbook.createCellStyle();
            wStyle.setAlignment(HorizontalAlignment.CENTER);
            wStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            wStyle.setWrapText(true);

            wStyle.setBorderLeft(BorderStyle.THIN);
            wStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderTop(BorderStyle.THIN);
            wStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderRight(BorderStyle.THIN);
            wStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());

            wStyle.setBorderBottom(BorderStyle.THIN);
            wStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());

            for (int i = 0; i < wRowList.size(); i++) {
                Row wHSSFRow = mSheet.createRow(i + 1);
                for (int j = 0; j < (wRowList.get(i)).size(); j++) {
                    Cell wHSSFCell = wHSSFRow.createCell(j);
                    wHSSFCell.setCellValue(wRowList.get(i).get(j));
                    wHSSFCell.setCellStyle(wStyle);
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 输出数据
     */
    public static void Export(OutputStream wOutput) {
        try {
            mWorkbook.write(wOutput);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void YJ_CreateTitle(String wTitleName) {
        try {
            mWorkbook = new HSSFWorkbook();
            mItemStyle = ItemStyle();
            mGroupStyle = GroupStyle();

            mSheet = mWorkbook.createSheet("机车预检报告");

            CellRangeAddress wRegion = new CellRangeAddress(0, 0, 0, 11);
            mSheet.addMergedRegion(wRegion);

            Row wRow = mSheet.createRow(0);
            wRow.setHeight((short) 500);
            Cell wCell = wRow.createCell(0);
            wCell.setCellValue(wTitleName);
            wCell.setCellStyle(TitleStyle());
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void YJ_CreateHeaders(String[] wHeaders) {
        try {
            Row wRow = mSheet.createRow(1);
            wRow.setHeight((short) 500);
            mHeads = wHeaders;

            CellStyle wStyle = HeadStyle();
            for (int i = 0; i < mHeads.length; i++) {
                Cell wCell = wRow.createCell(i);
                wCell.setCellValue(mHeads[i]);
                wCell.setCellStyle(wStyle);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void YJ_CreatePartPoint(String wNumber, String wName, int wRowNum) {
        try {
            Row wRow = mSheet.createRow(wRowNum);
            wRow.setHeight((short) 350);

            CellRangeAddress wRegion = new CellRangeAddress(wRowNum, wRowNum, 1, 11);
            mSheet.addMergedRegion(wRegion);

            Cell wCell = wRow.createCell(0);
            wCell.setCellValue(wNumber);
            wCell.setCellStyle(Style6());

            RegionUtil.setBorderRight(BorderStyle.THIN, wRegion, mSheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, wRegion, mSheet);

            wCell = wRow.createCell(1);
            wCell.setCellValue(wName);
            wCell.setCellStyle(mStepStyle);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void YJ_CreateGroup(String wNumber, String wName, int wRowNum) {
        try {
            Row wRow = mSheet.createRow(wRowNum);

            CellRangeAddress wRegion = new CellRangeAddress(wRowNum, wRowNum, 1, 11);
            mSheet.addMergedRegion(wRegion);

            RegionUtil.setBorderRight(BorderStyle.THIN, wRegion, mSheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, wRegion, mSheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, wRegion, mSheet);

            Cell wCell = wRow.createCell(0);
            wCell.setCellValue(wNumber);
            wCell.setCellStyle(mItemStyle);

            wCell = wRow.createCell(1);
            wCell.setCellValue(wName);
            wCell.setCellStyle(mGroupStyle);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void YJ_CreatePartPoint(String wNumber, String wName, int wRowNum, int wLength) {
        try {
            Row wRow = mSheet.createRow(wRowNum);
            wRow.setHeight((short) 350);

            CellRangeAddress wRegion = new CellRangeAddress(wRowNum, wRowNum, 1, wLength - 1);
            mSheet.addMergedRegion(wRegion);

            Cell wCell = wRow.createCell(0);
            wCell.setCellValue(wNumber);
            wCell.setCellStyle(Style6());

            RegionUtil.setBorderRight(BorderStyle.THIN, wRegion, mSheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, wRegion, mSheet);

            wCell = wRow.createCell(1);
            wCell.setCellValue(wName);
            wCell.setCellStyle(mStepStyle);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void YJ_CreateGroup(String wNumber, String wName, int wRowNum, int wLength) {
        try {
            Row wRow = mSheet.createRow(wRowNum);

            CellRangeAddress wRegion = new CellRangeAddress(wRowNum, wRowNum, 1, wLength - 1);
            mSheet.addMergedRegion(wRegion);

            RegionUtil.setBorderRight(BorderStyle.THIN, wRegion, mSheet);
            RegionUtil.setBorderBottom(BorderStyle.THIN, wRegion, mSheet);
            RegionUtil.setBorderTop(BorderStyle.THIN, wRegion, mSheet);

            Cell wCell = wRow.createCell(0);
            wCell.setCellValue(wNumber);
            wCell.setCellStyle(mItemStyle);

            wCell = wRow.createCell(1);
            wCell.setCellValue(wName);
            wCell.setCellStyle(mGroupStyle);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static CellStyle mTitleStyle = null;
    public static CellStyle mHeadStyle = null;
    public static CellStyle mStepStyle = null;
    public static CellStyle mGroupStyle = null;
    public static CellStyle mItemStyle = null;
    public static CellStyle mNoneStyle = null;
    public static CellStyle mLinkStyle = null;
    public static CellStyle mStyle7 = null;
    public static CellStyle mStyle8 = null;
    public static CellStyle mStyle9 = null;
    public static CellStyle mStyle10 = null;
    public static CellStyle mLeftStyle = null;

    public static CreationHelper mCreateHelper = null;

    /**
     * 输出Excel行数据
     */
    public static void WriteRowItem(List<String> wValueList, int wRowNum) {
        try {
            Row wRow = mSheet.createRow(wRowNum);
            wRow.setHeight((short) 375);

            for (String wValue : wValueList) {
                if (wValue == null) {
                    wValue = "";
                }
            }

            String wValue;
            for (int i = 0; i < wValueList.size(); i++) {
                wValue = wValueList.get(i);
                if (wValue == null) {
                    wValue = "";
                }
                if (wValue.contains(".jpg") || wValue.contains(".png")
                        || wValue.contains(".jpeg")) {

                    String[] wPics = wValue.split(",");
                    int wIndex = 0;
                    for (String wPic : wPics) {

                        String wFileName = MESFileUtils.GetFileName(wPic);
                        String wSuffix = MESFileUtils.GetSuffiex(wPic);
                        String wAddress = StringUtils.Format("picture/{0}.{1}", wFileName, wSuffix);

                        Cell wCell = wRow.createCell(i + wIndex);
                        wCell.setCellValue("图片" + (wIndex + 1));
                        HSSFHyperlink wLink = (HSSFHyperlink) mCreateHelper.createHyperlink(HyperlinkType.FILE);
                        wLink.setAddress(wAddress);
                        wCell.setHyperlink(wLink);
                        wCell.setCellStyle(mLinkStyle);

                        wIndex++;
                    }
                } else {
                    Cell wCell = wRow.createCell(i);
                    wCell.setCellValue(wValue);
                    wCell.setCellStyle(mItemStyle);
                }
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void APSBom_CreateHeaders(String wPartNo) {
        try {
            // 1,先创建工作簿
            mWorkbook = new HSSFWorkbook();
            mItemStyle = ItemStyle();
            mStyle7 = Style7();
            mStyle8 = Style8();
            mStyle9 = Style9();
            mStyle10 = Style10();
            // 2.创建sheet文件
            mSheet = mWorkbook.createSheet(wPartNo);
            // 创建第一行
            Row wRow = mSheet.createRow(0);
            wRow.setHeight((short) 500);

            CellStyle wStyle7 = mStyle7;
            CellStyle wStyle8 = mStyle8;

            Map<String, Integer> wValueMap = new LinkedHashMap<String, Integer>() {
                /**
                 * 序列号
                 */
                private static final long serialVersionUID = 1L;

                {
                    put("BOM类型", 7);
                    put("车型", 7);
                    put("修程", 7);
                    put("局段信息", 7);
                    put("工厂", 7);
                    put("WBS元素", 7);
                    put("工位", 7);
                    put("工序描述", 7);
                    put("物料编码", 7);
                    put("物料名称", 8);
                    put("数量", 8);
                    put("单位", 8);
                    put("必换/偶换", 8);
                    put("必修/偶修", 8);
                    put("评估类型", 8);
                    put("是否互换件", 8);
                    put("是否超修程", 8);
                    put("领料部门", 8);
                    put("仓库号", 8);
                    put("质量损失大类", 8);
                    put("质量损失小类", 8);
                    put("客供料标识", 8);
                }
            };

            int wIndex = 0;
            Cell wCell;
            for (String wKey : wValueMap.keySet()) {
                wCell = wRow.createCell(wIndex++);
                wCell.setCellValue(wKey);
                wCell.setCellStyle(wValueMap.get(wKey) == 7 ? wStyle7 : wStyle8);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void APSBom_CreateTips() {
        try {
            Row wRow = mSheet.createRow(1);

            List<String> wValueList = new ArrayList<>(Arrays.asList("1、新造\r\n2、检修\r\n9、计划外", "车型编号",
                    "目前只允许5、6", "2位的局段编号", "固定值1900", "每台车对应的WBS号", "工位编号", "工序描述", "零部件编号", "物料描述，仅在EXCEL显示，系统不检查",
                    "用量", "用量单位", "必换-1/偶换-2", "委外必修-1\r\n委外偶修-2\r\n自修必修-3\r\n自修偶修-4\r\n其他不填",
                    "必换/偶换件：常规新件，\r\n委外修件：修复旧件，\r\n自修件：可用旧件\r\n互换件：修复旧件", "互换件填X", "超修程填X", "固定值0001",
                    "新造1100\r\n检修1200", "报废001\r\n返工返修002\r\n停产003\r\n内部质量收入004",
                    "设计差错01\r\n工艺差错02\r\n制造差错03\r\n供方原因04\r\n其他原因05", "客供料填X"));

            for (int i = 0; i < wValueList.size(); i++) {
                Cell wCell = wRow.createCell(i);
                wCell.setCellValue(wValueList.get(i));
                wCell.setCellStyle(mItemStyle);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void APSBom_CreateOptions() {
        try {
            Row wRow = mSheet.createRow(2);

            String wValue1 = "必输";
            String wValue2 = "可选";

            CellStyle wStyle9 = mStyle9;
            CellStyle wStyle10 = mStyle10;

            int wIndex = 0;
            Cell wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue1);
            wCell.setCellStyle(wStyle9);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);

            wCell = wRow.createCell(wIndex++);
            wCell.setCellValue(wValue2);
            wCell.setCellStyle(wStyle10);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    public static void APSBom_CreateItems(List<String> wValueList, int wRowNum) {
        try {
            Row wRow = mSheet.createRow(wRowNum);

            for (int i = 0; i < wValueList.size(); i++) {
                Cell wCell = wRow.createCell(i);
                wCell.setCellValue(wValueList.get(i));
                wCell.setCellStyle(mItemStyle);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 绘制工序项点模板
     */
    public static void SCGC_WriteModel(List<List<String>> wSourceList, FileOutputStream wFileOutputStream,
                                       FileInputStream wInputStream) {
        try {
            if (wSourceList == null || wSourceList.size() <= 0) {
                return;
            }

            XSSFWorkbook wWorkbook = new XSSFWorkbook(wInputStream);

            mItemStyle = ItemStyle(wWorkbook);

            mSheet = wWorkbook.getSheet("导入模板");

            Row wRow = null;
            Cell wCell = null;

            for (int i = 1; i < wSourceList.size(); i++) {
                wRow = mSheet.createRow(i);
                for (int j = 0; j < wSourceList.get(i).size(); j++) {
                    wCell = wRow.createCell(j);
                    wCell.setCellValue(wSourceList.get(i).get(j));
                    wCell.setCellStyle(mItemStyle);
                }
            }

            wWorkbook.write(wFileOutputStream);

            wWorkbook.close();
            wInputStream.close();
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    private static CellStyle ItemStyle(XSSFWorkbook wWorkbook) {
        XSSFCellStyle wStyle = wWorkbook.createCellStyle();
        try {
            wStyle.setAlignment(HorizontalAlignment.CENTER);
            wStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            wStyle.setWrapText(true);
            wStyle.setBorderLeft(BorderStyle.THIN);
            wStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex());
            wStyle.setBorderTop(BorderStyle.THIN);
            wStyle.setTopBorderColor(IndexedColors.BLACK.getIndex());
            wStyle.setBorderBottom(BorderStyle.THIN);
            wStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex());
            wStyle.setBorderRight(BorderStyle.THIN);
            wStyle.setRightBorderColor(IndexedColors.BLACK.getIndex());
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wStyle;
    }

    public static CellStyle mItemStyle_Purple;
    public static CellStyle mItemStyle_Red;
    public static CellStyle mItemStyle_Blue;
    public static CellStyle mItemStyle_NoBorder;

    /**
     * 设置Excel列的宽度适应A4纸张
     */
    private static void SetColumnWidthToA4(int wLength) {
        try {
            for (int i = 0; i < wLength; i++) {
                mSheet.setColumnWidth(i, 2340);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 设置Excel列的宽度适应A4纸张
     */
    private static void SetColumnWidth(int wLength, int wWidth) {
        try {
            for (int i = 0; i < wLength; i++) {
                mSheet.setColumnWidth(i, wWidth);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 添加图片到单元格
     */
    private static void AddPicture(int wLastRow, String legend, Row wRow) {
        try {
            String[] wStrs = legend.split(";");
            wRow.setHeight((short) (2000 * wStrs.length));

            int wIndex = 0;
            for (String wStr : wStrs) {
                // ①获取后缀
                String wSuffiex = wStr.substring(wStr.lastIndexOf('.') + 1);

                ByteArrayOutputStream wByteArrayOut = new ByteArrayOutputStream();
                BufferedImage wBufferImg = ImageIO.read(new File(wStr));
                ImageIO.write(wBufferImg, wSuffiex, wByteArrayOut);

                HSSFSheet wSheet1 = (HSSFSheet) mSheet;
                // 画图的顶级管理器，一个sheet只能获取一个（一定要注意这点）
                HSSFPatriarch wPatriarch = wSheet1.createDrawingPatriarch();
                // anchor主要用于设置图片的属性
                HSSFClientAnchor wAnchor = new HSSFClientAnchor(0, wIndex * (255 / wStrs.length), 1023,
                        (wIndex + 1) * (255 / wStrs.length), (short) 5, wLastRow, (short) 5, wLastRow);
                // 注意：这个方法在新版本的POI中参数类型改成了（AnchorType anchorType）
                wAnchor.setAnchorType(AnchorType.DONT_MOVE_AND_RESIZE);

                // 插入图片
                int wPicIndex = 0;
                if (wSuffiex.equals("jpg") || wSuffiex.equals("jpeg")) {
                    wPicIndex = HSSFWorkbook.PICTURE_TYPE_JPEG;
                } else if (wSuffiex.equals("png")) {
                    wPicIndex = HSSFWorkbook.PICTURE_TYPE_PNG;
                }

                wPatriarch.createPicture(wAnchor, mWorkbook.addPicture(wByteArrayOut.toByteArray(), wPicIndex));

                wIndex++;
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 导出Excel数据，通用函数
     */
    public static String ExportData(List<MyExcelSheet> wMyExcelSheetList, String wShortFileName) {
        String wResult = "";
        try {
            if (wMyExcelSheetList == null || wMyExcelSheetList.size() <= 0) {
                return wResult;
            }

            SimpleDateFormat wSimpleDateFormat = new SimpleDateFormat("yyyyMMddHHmmss");
            String wCurTime = wSimpleDateFormat.format(Calendar.getInstance().getTime());

            String wFileName = StringUtils.Format("{1}{0}.xls", wCurTime, wShortFileName);
            String wDirePath = StringUtils.Format("{0}static/export/",
                    Constants.getConfigPath().replace("config/", ""));

            File wDirFile = new File(wDirePath);
            if (!wDirFile.exists()) {
                wDirFile.mkdirs();
            }

            String wFilePath = StringUtils.Format("{0}{1}", wDirePath, wFileName);
            File wNewFile = new File(wFilePath);
            wNewFile.createNewFile();

            FileOutputStream wFileOutputStream = new FileOutputStream(wNewFile);

            mWorkbook = new HSSFWorkbook();

            mTitleStyle = TitleStyle();
            mHeadStyle = HeadStyle();
            mItemStyle = ItemStyle();

            for (MyExcelSheet wMyExcelSheet : wMyExcelSheetList) {
                CreateSheet(wMyExcelSheet);
            }

            Export(wFileOutputStream);

            wResult = StringUtils.Format("/{0}/export/{1}",
                    Configuration.readConfigString("project.name", "application"), wFileName);
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 创建一张表格数据
     */
    private static void CreateSheet(MyExcelSheet wMyExcelSheet) {
        try {
            mSheet = mWorkbook.createSheet(wMyExcelSheet.SheetName);

            int wRowIndex = 0;
            Row wRow = null;
            Cell wCell = null;

            // 设置列宽
            SetColumnWidth(wMyExcelSheet.HeaderList.size(), 7114);

            if (!StringUtils.isEmpty(wMyExcelSheet.TitleName)) {
                CellRangeAddress wRegion = new CellRangeAddress(0, 0, 0, wMyExcelSheet.HeaderList.size() - 1);
                mSheet.addMergedRegion(wRegion);

                wRow = mSheet.createRow(wRowIndex);
                wRow.setHeight((short) 500);
                wCell = wRow.createCell(0);
                wCell.setCellValue(wMyExcelSheet.TitleName);
                wCell.setCellStyle(mTitleStyle);

                wRowIndex++;
            }

            wRow = mSheet.createRow(wRowIndex);
            wRow.setHeight((short) 550);
            for (int j = 0; j < wMyExcelSheet.HeaderList.size(); j++) {
                wCell = wRow.createCell(j);
                wCell.setCellValue(wMyExcelSheet.HeaderList.get(j));
                wCell.setCellStyle(mHeadStyle);
            }
            wRowIndex++;

            for (List<String> wValueList : wMyExcelSheet.DataList) {
                WriteRowItem(wValueList, wRowIndex++);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 填充指定Excel模板
     *
     * @param templatePath     Excel模板文件路径
     * @param startRow         开始填充的行号（从0开始）
     * @param data             每一行需要填充的数据，每一行一个字符串，用逗号隔开
     * @param outputPath       输出的Excel文件路径
     * @param wReplaceDataList 替换单元格数据的集合
     */
    public static void fillTemplate(String templatePath, int startRow, List<String> data, String outputPath,
                                    List<ExcelCellData> wReplaceDataList) {
        try {
            // 1. 读取模板文件
            FileInputStream fis = new FileInputStream(templatePath);
            Workbook workbook = new XSSFWorkbook(fis);
            Sheet sheet = workbook.getSheetAt(0);

            // 2. 从指定行开始，依次将每行的数据填入每行的单元格，且填入数据的单元格带有边框
            for (String datum : data) {
                Row row = sheet.getRow(startRow);
                if (row == null) {
                    row = sheet.createRow(startRow);
                }
                String[] rowData = datum.split(","); // 假设数据以逗号分隔
                int cellIndex = 0;
                for (String cellData : rowData) {
                    Cell cell = row.getCell(cellIndex);
                    if (cell == null) {
                        cell = row.createCell(cellIndex);
                    }
                    cell.setCellValue(cellData);
                    cellIndex++;
                }
                // 设置边框样式
                for (int j = 0; j < rowData.length; j++) {
                    CellStyle style = workbook.createCellStyle();
                    style.setBorderTop(BorderStyle.THIN);
                    style.setBorderBottom(BorderStyle.THIN);
                    style.setBorderLeft(BorderStyle.THIN);
                    style.setBorderRight(BorderStyle.THIN);

                    Font wFont = workbook.createFont();
                    wFont.setBold(false);
                    wFont.setFontName("Calibri");
                    wFont.setFontHeightInPoints((short) 10);
                    style.setFont(wFont);

                    row.getCell(j).setCellStyle(style);
                }
                //行号+1
                startRow++;
            }

            // 3.遍历替换单元格数据集，依次替换单元格数据
            if (wReplaceDataList != null && wReplaceDataList.size() > 0) {
                for (ExcelCellData wExcelCellData : wReplaceDataList) {
                    Row row = sheet.getRow(wExcelCellData.row);
                    if (row == null) {
                        row = sheet.createRow(wExcelCellData.row);
                    }

                    Cell cell = row.getCell(wExcelCellData.col);
                    if (cell == null) {
                        cell = row.createCell(wExcelCellData.col);
                    }

                    cell.setCellValue(wExcelCellData.value);
                }
            }

            // 3. 输出到指定的输出文件路径
            FileOutputStream fos = new FileOutputStream(outputPath);
            workbook.write(fos);
            fos.close();
            workbook.close();
        } catch (Exception ex) {
            logger.error("fillTemplate", ex);
        }
    }

    /**
     * 填充指定Excel模板
     *
     * @param templatePath             Excel模板文件路径
     * @param outputPath               输出的Excel文件路径
     * @param wSheetIndexList          需填充的Sheet下标集合
     * @param wToRemoveSheetIndexList  待删除的Sheet下标集合
     * @param wReplaceDataListMap      待替换单元格数据的集合(Key：单元格Sheet下标 Value：待替换的单元格数据)
     * @param wToRemoveRowIndexListMap 待删除的行数据集合(Key：单元格Sheet下标 Value：待删除的行下标集合)
     * @param wRegionMap
     */
    public static void fillTemplate_V1(String templatePath, String outputPath, List<Integer> wSheetIndexList,
                                       List<Integer> wToRemoveSheetIndexList,
                                       Map<Integer, List<ExcelCellData>> wReplaceDataListMap, Map<Integer,
            List<Integer>> wToRemoveRowIndexListMap, Map<Integer, List<CellRangeAddress>> wRegionMap) {
        try {
            // 1. 读取模板文件
            FileInputStream fis = new FileInputStream(templatePath);
            Workbook workbook = new XSSFWorkbook(fis);

            for (int wIndex : wSheetIndexList) {
                Sheet sheet = workbook.getSheetAt(wIndex);
                if (!wReplaceDataListMap.containsKey(wIndex)) {
                    continue;
                }
                // 2.遍历替换单元格数据集，依次替换单元格数据
                List<ExcelCellData> wReplaceDataList = wReplaceDataListMap.get(wIndex);
                if (wReplaceDataList != null && wReplaceDataList.size() > 0) {
                    for (ExcelCellData wExcelCellData : wReplaceDataList) {
                        Row row = sheet.getRow(wExcelCellData.row);
                        if (row == null) {
                            row = sheet.createRow(wExcelCellData.row);
                        }

                        Cell cell = row.getCell(wExcelCellData.col);
                        if (cell == null) {
                            cell = row.createCell(wExcelCellData.col);
                        }

                        cell.setCellValue(wExcelCellData.value);
                    }
                }
            }

            // 3.遍历待删除的行数据集合，删除指定行
            for (int wIndex : wSheetIndexList) {
                Sheet sheet = workbook.getSheetAt(wIndex);
                if (!wToRemoveRowIndexListMap.containsKey(wIndex)) {
                    continue;
                }
                List<Integer> wToRemoveRowList = wToRemoveRowIndexListMap.get(wIndex);
                for (Integer wRowIndex : wToRemoveRowList) {
                    sheet.shiftRows(wRowIndex + 1, sheet.getLastRowNum(), -1);
                }
            }

            // 4.遍历待删除的Sheet集合，依次删除指定Sheet
            // 逆序排序
            wToRemoveSheetIndexList.sort(Comparator.comparing(p -> p, Comparator.reverseOrder()));
            for (int wIndex : wToRemoveSheetIndexList) {
                workbook.removeSheetAt(wIndex);
            }

            //合并单元格
            for (int wSheetIndex : wRegionMap.keySet()) {
                Sheet sheet = workbook.getSheetAt(wSheetIndex);
                List<CellRangeAddress> cellRangeAddresses = wRegionMap.get(wSheetIndex);
                for (CellRangeAddress cellRangeAddress : cellRangeAddresses) {
                    sheet.addMergedRegion(cellRangeAddress);
                }
            }

            // 3. 输出到指定的输出文件路径
            FileOutputStream fos = new FileOutputStream(outputPath);
            workbook.write(fos);
            fos.close();
            workbook.close();
        } catch (Exception ex) {
            logger.error("fillTemplate", ex);
        }
    }

    /**
     * 填充空元素（向前补齐）
     */
    public static List<String> fillEmptyStrings(List<String> strings) {
        List<String> filledStrings = new ArrayList<>(strings);
        try {
            for (int i = 1; i < filledStrings.size(); i++) {
                if (filledStrings.get(i).isEmpty()) {
                    for (int j = i - 1; j >= 0; j--) {
                        if (!filledStrings.get(j).isEmpty()) {
                            filledStrings.set(i, filledStrings.get(j));
                            break;
                        }
                    }
                }
            }
        } catch (Exception ex) {
            logger.error("fillEmptyStrings", ex);
        }
        return filledStrings;
    }

//    public static void main(String[] args) throws IOException {
//        List<String> strings = new ArrayList<>();
//        strings.add("A");
//        strings.add("");
//        strings.add("");
//        strings.add("B");
//        strings.add("");
//        strings.add("C");
//
//        List<String> filledStrings = fillEmptyStrings(strings);
//
//        // 输出结果
//        for (String s : filledStrings) {
//            System.out.print(s + " ");
//        }
//    }
}